/*
 * @(#) DataImporter.java 2012-7-26 娑撳﹤宕�1:08:32
 *
 * Copyright 2012 Rockwell Automation, Inc. All rights reserved.
 * Rockwell Automation PROPRIETARY/CONFIDENTIAL. Use is subject to license terms.
 */package com.mesd.killer.importer;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.net.URL;
import java.net.URLConnection;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

public class GameDetailReader
{
	String YAPEI_URL = "http://bf.bet007.com/phone/Handicap.aspx?ID=";

	String OVERUNDER_URL = "http://bf.bet007.com/phone/OverUnder.aspx?ID=";

	String OUPEI_URL = "http://bf.bet007.com/phone/1x2.aspx?ID=";

	public static void main(String[] args)
		throws Exception
	{
		GameDetailReader reader = new GameDetailReader();
//		 String[] leagueNames = {"欧洲杯预选赛"};
//		 for(int i=0;i<leagueNames.length;i++)
//		 {
//		 reader.importDetailData(leagueNames[i]);
//		 }

		reader.saveDetailData();
//		reader.importPartDetailData();
	}

	public ArrayList readYaPei(long gameID)
		throws Exception
	{
		String u = YAPEI_URL + gameID;
		URL url = new URL(u);
		URLConnection urlConn = url.openConnection();
		urlConn.connect();
		BufferedReader da = new BufferedReader(new InputStreamReader(urlConn.getInputStream(),
			"UTF-8"));
		String line = da.readLine();
		da.close();
		return ImportUtility.getYaPeiObjects(line);
	}

	public ArrayList readOuPei(long gameID)
		throws Exception
	{
		String u = OUPEI_URL + gameID;
		URL url = new URL(u);
		URLConnection urlConn = url.openConnection();
		urlConn.connect();
		BufferedReader da = new BufferedReader(new InputStreamReader(urlConn.getInputStream(),
			"UTF-8"));
		String line = da.readLine();
		da.close();
		return ImportUtility.getOuPeiObjects(line);
	}

	public ArrayList readDaXiao(long gameID)
		throws Exception
	{
		String u = OVERUNDER_URL + gameID;
		URL url = new URL(u);
		URLConnection urlConn = url.openConnection();
		urlConn.connect();
		BufferedReader da = new BufferedReader(new InputStreamReader(urlConn.getInputStream(),
			"UTF-8"));
		String line = da.readLine();
		da.close();
		return ImportUtility.getDaXiaoObjects(line);
	}

	public void importGameDetail(Connection conn, long gameID)
		throws Exception
	{
		try
		{
			Statement stmt = conn.createStatement();
			ArrayList<YapeiObject> yaPeiObjects = readYaPei(gameID);
			if (yaPeiObjects != null)
			{
				for (int i = 0; i < yaPeiObjects.size(); i++)
				{
					YapeiObject yaPeiObject = yaPeiObjects.get(i);
					if (yaPeiObject == null)
						continue;
					String sqlYaPei = "insert into game_detail_yapei values(" + gameID + ",'"
						+ yaPeiObject.getCompanyName() + "','" + yaPeiObject.getBetCompanyId()
						+ "'," + yaPeiObject.getHomeChupei() + ",'" + yaPeiObject.getChupan()
						+ "'," + yaPeiObject.getAwayChupei() + "," + yaPeiObject.getHomeJishi()
						+ ",'" + yaPeiObject.getJishi() + "'," + yaPeiObject.getAwayJishi()
						+ ",NULL,NULL,NULL,NULL,NULL,NULL)";
					String sqlSelect = "select id from game_detail_yapei where game_id = " + gameID
						+ " and company_name = '" + yaPeiObject.getCompanyName() + "'";
					ResultSet rs = stmt.executeQuery(sqlSelect);
					if (rs.next())
					{
						long id = rs.getLong("id");
						sqlYaPei = "update game_detail_yapei set home_jishi = "
							+ yaPeiObject.getHomeJishi() + " , jishi = '" + yaPeiObject.getJishi()
							+ "' , visit_jishi = " + yaPeiObject.getAwayJishi() + " where id = "
							+ id;
					}
					rs.close();
					stmt.addBatch(sqlYaPei);
				}
			}

			ArrayList<OupeiObject> ouPeiObjects = readOuPei(gameID);
			if (ouPeiObjects != null)
			{
				for (OupeiObject ouPeiObject : ouPeiObjects)
				{
					String sqlOuPei = "insert into game_detail_oupei values(" + gameID + ",'"
						+ ouPeiObject.getBetCompanyName() + "','" + ouPeiObject.getBetCompanyId()
						+ "'," + ouPeiObject.getChupanWin() + "," + ouPeiObject.getChupanDraw()
						+ "," + ouPeiObject.getChupanLost() + "," + ouPeiObject.getJishiWin() + ","
						+ ouPeiObject.getJishiDraw() + "," + ouPeiObject.getJishiLost()
						+ ",NULL,NULL,NULL,NULL,NULL,NULL)";
					String sqlSelect = "select id from game_detail_oupei where game_id = " + gameID
						+ " and company_name = '" + ouPeiObject.getBetCompanyName() + "'";
					ResultSet rs = stmt.executeQuery(sqlSelect);
					if (rs.next())
					{
						long id = rs.getLong("id");
						sqlOuPei = "update game_detail_oupei set jishi_win = "
							+ ouPeiObject.getJishiWin() + " , jishi_draw = "
							+ ouPeiObject.getJishiDraw() + " , jishi_lost = "
							+ ouPeiObject.getJishiLost() + " where id = " + id;
					}
					rs.close();
					stmt.addBatch(sqlOuPei);
				}
			}

			ArrayList<DaXiaoObject> daXiaoObjects = readDaXiao(gameID);
			if (daXiaoObjects != null)
			{
				for (int i = 0; i < daXiaoObjects.size(); i++)
				{
					DaXiaoObject daXiaoObject = daXiaoObjects.get(i);
					if (daXiaoObject == null)
						continue;
					String sqlDaXiao = "insert into game_detail_daxiao values(" + gameID + ",'"
						+ daXiaoObject.getBetCompanyName() + "','" + daXiaoObject.getBetCompanyId()
						+ "'," + daXiaoObject.getDaChupei() + ",'" + daXiaoObject.getChupan()
						+ "'," + daXiaoObject.getXiaoChupei() + "," + daXiaoObject.getDaJishi()
						+ ",'" + daXiaoObject.getJishi() + "'," + daXiaoObject.getXiaoJishi()
						+ ",NULL,NULL,NULL,NULL,NULL,NULL)";
					String sqlSelect = "select id from game_detail_daxiao where game_id = "
						+ gameID + " and company_name = '" + daXiaoObject.getBetCompanyName() + "'";
					ResultSet rs = stmt.executeQuery(sqlSelect);
					if (rs.next())
					{
						long id = rs.getLong("id");
						sqlDaXiao = "update game_detail_daxiao set da_jishi = "
							+ daXiaoObject.getDaJishi() + ",jishi = '" + daXiaoObject.getJishi()
							+ "', xiao_jishi = " + daXiaoObject.getXiaoJishi() + " where id = "
							+ id;
					}
					rs.close();
					stmt.addBatch(sqlDaXiao);
				}
			}

			stmt.executeBatch();
			stmt.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}

	// 闇�姹囨�鍗虫椂Detail鏁版嵁
	public void sumerizeDetailData(ArrayList gameIDs, Connection conn)
		throws Exception
	{
		Statement stmt = conn.createStatement();
		int i = 0;
		for (; i < gameIDs.size(); i++)
		{
			long gameId = (Long) gameIDs.get(i);
			String sqlDelete = "delete from game_detail_data where game_id=" + gameId;
			stmt.addBatch(sqlDelete);
		}
		if (i > 0)
		{
			stmt.executeBatch();
		}
		int j = 0;
		for (; j < gameIDs.size(); j++)
		{
			long gameId = (Long) gameIDs.get(j);
			String sqlInsert = "insert into game_detail_data(game_id, company_name, chupan_host_shuiwei, chupan_pankou, chupan_visit_shuiwei, jishi_host_shuiwei, jishi_pankou, jishi_visit_shuiwei, "
				+ "chupan_host_odds,chupan_draw_odds,chupan_visit_odds, jishi_host_odds, jishi_draw_odds, jishi_visit_odds,"
				+ "chupan_big_shuiwei, chupan_daxiao_pankou, chupan_small_shuiwei, jishi_big_shuiwei, jishi_daxiao_pankou, jishi_small_shuiwei)"
				+ "select yapei.game_id, yapei.company_name, yapei.home_chupei, yapei.chupan, yapei.visit_chupei, yapei.home_jishi, yapei.jishi, yapei.visit_jishi,"
				+ "oupei.chupan_win, oupei.chupan_draw, oupei.chupan_lost, oupei.jishi_win, oupei.jishi_draw, oupei.jishi_lost,"
				+ "daxiao.da_chupei, daxiao.chupan, daxiao.xiao_chupei, daxiao.da_jishi, daxiao.jishi, daxiao.xiao_jishi "
				+ "from game_detail_yapei yapei, game_detail_daxiao daxiao, game_detail_oupei oupei "
				+ "where yapei.game_id=oupei.game_id and yapei.game_id=daxiao.game_id and yapei.company_name=oupei.company_name and yapei.company_name=daxiao.company_name "
				+ "and yapei.company_name in ('SB','Bet365','澳彩') and yapei.game_id=" + gameId;
			stmt.addBatch(sqlInsert);
		}
		if (j > 0)
		{
			stmt.executeBatch();
		}
		stmt.close();
	}

	// 闇�姣忛殧5鍒嗛挓鍒锋柊鍗虫椂Detail鏁版嵁
	public void saveDetailData()
	{
		ArrayList gameIDs = new ArrayList();
		Connection conn = DBUtil.getConnection();
		Statement stmt = null;
		try
		{
			stmt = conn.createStatement();
			String sql = "select game_id from game_result where state!=-1 and datediff(day, GETDATE(), game_time)<=1";
			
//			String sql1 = "select game_id from game_result where state!=-1 and datediff (MI, GETDATE(),game_time)>=-3000 and datediff(day, GETDATE(), game_time)<=1";
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next())
			{
				long gameID = rs.getLong("game_id");
				gameIDs.add(gameID);
			}
			rs.close();
			stmt.close();
			for (int i = 0; i < gameIDs.size(); i++)
			{
				long gameId = (Long) gameIDs.get(i);
				importGameDetail(
					conn, gameId);

			}
			sumerizeDetailData(
				gameIDs, conn);

		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		DBUtil.close(
			conn, stmt);
	}

	public void importDetailData(String leagueName)
		throws Exception
	{
		ArrayList gameIDs = new ArrayList();
		Connection conn = DBUtil.getConnection();
		Statement stmt = conn.createStatement();
		String sql = "select game_id from game_result where state=-1 and league_name='"
			+ leagueName + "'";
		ResultSet rs = stmt.executeQuery(sql);
		while (rs.next())
		{
			long gameID = rs.getLong("game_id");
			gameIDs.add(gameID);
		}
		rs.close();
		stmt.close();
		for (int i = 0; i < gameIDs.size(); i++)
		{
			long gameId = (Long) gameIDs.get(i);
			importGameDetail(
				conn, gameId);

		}
		sumerizeDetailData(
			gameIDs, conn);
		conn.close();
	}
	
	public void importPartDetailData()
		throws Exception
	{
		ArrayList gameIDs = new ArrayList();
		Connection conn = DBUtil.getConnection();
		Statement stmt = conn.createStatement();
		String sql = "select game_id from game_result where state=-1 and pankou_result is null";
		ResultSet rs = stmt.executeQuery(sql);
		while (rs.next())
		{
			long gameID = rs.getLong("game_id");
			gameIDs.add(gameID);
		}
		rs.close();
		stmt.close();
		for (int i = 0; i < gameIDs.size(); i++)
		{
			long gameId = (Long) gameIDs.get(i);
			importGameDetail(
				conn, gameId);

		}
		sumerizeDetailData(
			gameIDs, conn);
		conn.close();
	}

}
